package com.jhg.drgclinicalpathway.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jhg.drgclinicalpathway.domain.V_C成本项目目录;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface V_C成本项目目录Dao extends BaseMapper<V_C成本项目目录> {

    @Select({"select distinct  lb as fb from " +
            "V_C成本项目目录 where LEN(lb)>0 "})
    public List<Map<String, Object>> getCostFYLB();

    @Select({"select top ${size} 病组编码,入组名称,count(1) 病例数,sum(标准支付金额) as 标准支付金额,sum(统筹报销) as 统筹报销,sum(drg基金支付) as drg基金支付," +
            "sum(drg盈亏) as drg盈亏,sum(总费用) as 总费用,sum(总成本) as 总成本,sum(总费用-总成本+drg盈亏) as 总盈亏 from  " +
            "(select  a.Drg编码 病组编码, a.drg名称 as 入组名称,a.Ddrg标准支付金额 as 标准支付金额,a.项目结算基金支付 as 统筹报销,医保基金drg支付 as drg基金支付," +
            "a.盈亏状况 as drg盈亏,a.住院总费用 as 总费用,(select sum(bb.zcb) from TMP_费用信息 aa " +
            "left join V_C成本项目目录 bb on aa.xmid=bb.sfxmid where aa.jzh=a.就诊号) 总成本,a.盈亏状况 as 总盈亏 " +
            "from VDCP_入组结算明细 a where a.drg名称 is not null and a.结算时间 between #{kssj} and #{jssj} " +
            ") as js group by 病组编码,入组名称 order by 病例数 desc "})
    public List<Map<String, Object>> getCostCBHZ(@Param("size") int size,
                                                 @Param("kssj") String kssj,
                                                 @Param("jssj") String jssj);


    @Select({"select cb.jzh,cb.结算时间,cb.科室,cb.姓名,cb.住院号,cb.主管医生,cb.病组编码,cb.病组名称,cb.标准支付金额,\n" +
            "cb.统筹报销,CONVERT(NUMERIC(18,2),cb.DRG基金支付) as DRG基金支付,cb.DRG盈亏,cb.结算金额,cb.成本金额,(cb.结算金额-cb.成本金额+DRG盈亏) as 盈亏 from\n" +
            "(select a.结算时间,b.科室,b.病人姓名 姓名,b.住院号,b.病人ID as jzh,b.医生 主管医生,b.病组编码,b.病组名称,\n" +
            "a.Ddrg标准支付金额 标准支付金额,a.项目结算基金支付 as 统筹报销,a.医保范围金额 as DRG基金支付,a.盈亏状况 as DRG盈亏,a.住院总费用 as 结算金额,\n" +
            "(select sum(bb.zcb) from TMP_费用信息 aa left join V_C成本项目目录 bb on aa.xmid=bb.sfxmid where aa.jzh=a.就诊号) 成本金额\n" +
            "from VDCP_入组结算明细 a, VDCP_病人信息 b where a.就诊号=b.病人ID and a.drg名称 is not null " +
            ") as cb where 结算时间 between #{kssj} and #{jssj} and cb.病组编码=#{bzbm} "})
    public List<Map<String, Object>> getCostCBHZ_HZmx(@Param("bzbm") String bzbm,
                                                    @Param("kssj") String kssj,
                                                    @Param("jssj") String jssj);
    @Select({"select  c.病人姓名,c.性别,c.年龄,c.科室,c.医生, b.bm 项目编码, a.XMMC as 项目名称,\n" +
            "b.gg 规格,b.cd as 厂家,b.zcb 成本单价,a.DJ 记账单价,\n" +
            "sum(a.SL) 数量,sum(a.SL*b.zcb) 成本金额,sum(a.JE) 记账金额 \n" +
            "from VDCP_入组结算明细 js \n" +
            "left join tmp_费用信息 a on js.就诊号=a.JZH \n" +
            "left join V_C成本项目目录 b on a.XMID=b.sfxmid \n" +
            "left join VDCP_病人信息 c on a.JZH=c.病人ID\n" +
            "where b.bm is not null and a.JZH=#{jzh} \n" +
            "group by  c.病人姓名,c.性别,c.年龄,c.科室,c.医生, b.bm, a.XMMC,\n" +
            "b.gg,b.cd,b.zcb,a.DJ "})
    public List<Map<String, Object>> getCostCBHZ_mx(@Param("jzh") String jzh);
}
